{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### LAB 6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ap_name</th>\n",
       "      <th>ap_mac</th>\n",
       "      <th>ssid</th>\n",
       "      <th>account</th>\n",
       "      <th>mac</th>\n",
       "      <th>linktime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>084f-0a37-30c0</td>\n",
       "      <td>084f0a3730c0</td>\n",
       "      <td>iLZU</td>\n",
       "      <td>qmleu16</td>\n",
       "      <td>ae2887</td>\n",
       "      <td>2021-04-02 09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>084f-0a37-30c0</td>\n",
       "      <td>084f0a3730c0</td>\n",
       "      <td>iLZU</td>\n",
       "      <td>eamrw19</td>\n",
       "      <td>fa341c</td>\n",
       "      <td>2021-04-02 09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0_no_name</td>\n",
       "      <td>5cc9999a3c80</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>9ce33f</td>\n",
       "      <td>2021-04-02 09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0_no_name</td>\n",
       "      <td>5cc9999a3c80</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>3ca616</td>\n",
       "      <td>2021-04-02 09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0_no_name</td>\n",
       "      <td>5cc9999a3c80</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>182195</td>\n",
       "      <td>2021-04-02 08:20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27545</th>\n",
       "      <td>yz_youyongguan_01</td>\n",
       "      <td>60da838bc730</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>f89a78</td>\n",
       "      <td>2021-04-02 09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27546</th>\n",
       "      <td>yz_youyongguan_02</td>\n",
       "      <td>60da838bba10</td>\n",
       "      <td>ChinaNet-WiFi</td>\n",
       "      <td></td>\n",
       "      <td>74adb7</td>\n",
       "      <td>2021-04-02 08:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27547</th>\n",
       "      <td>yz_youyongguan_02</td>\n",
       "      <td>60da838bba10</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>cc2d83</td>\n",
       "      <td>2021-04-02 08:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27548</th>\n",
       "      <td>yz_youyongguan_03</td>\n",
       "      <td>60da838bcc10</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>308454</td>\n",
       "      <td>2021-04-02 08:20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27549</th>\n",
       "      <td>yz_youyongguan_06</td>\n",
       "      <td>60da838bbe90</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>084acf</td>\n",
       "      <td>2021-04-02 09:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27550 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 ap_name        ap_mac           ssid  account     mac  \\\n",
       "0         084f-0a37-30c0  084f0a3730c0           iLZU  qmleu16  ae2887   \n",
       "1         084f-0a37-30c0  084f0a3730c0           iLZU  eamrw19  fa341c   \n",
       "2              0_no_name  5cc9999a3c80            LZU           9ce33f   \n",
       "3              0_no_name  5cc9999a3c80            LZU           3ca616   \n",
       "4              0_no_name  5cc9999a3c80            LZU           182195   \n",
       "...                  ...           ...            ...      ...     ...   \n",
       "27545  yz_youyongguan_01  60da838bc730            LZU           f89a78   \n",
       "27546  yz_youyongguan_02  60da838bba10  ChinaNet-WiFi           74adb7   \n",
       "27547  yz_youyongguan_02  60da838bba10            LZU           cc2d83   \n",
       "27548  yz_youyongguan_03  60da838bcc10            LZU           308454   \n",
       "27549  yz_youyongguan_06  60da838bbe90            LZU           084acf   \n",
       "\n",
       "                  linktime  \n",
       "0      2021-04-02 09:00:00  \n",
       "1      2021-04-02 09:00:00  \n",
       "2      2021-04-02 09:00:00  \n",
       "3      2021-04-02 09:00:00  \n",
       "4      2021-04-02 08:20:00  \n",
       "...                    ...  \n",
       "27545  2021-04-02 09:00:00  \n",
       "27546  2021-04-02 08:40:00  \n",
       "27547  2021-04-02 08:40:00  \n",
       "27548  2021-04-02 08:20:00  \n",
       "27549  2021-04-02 09:00:00  \n",
       "\n",
       "[27550 rows x 6 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_json('lzuwifi.json', lines=True)\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 将linktime改成只有时间，去掉日期"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ap_name</th>\n",
       "      <th>ap_mac</th>\n",
       "      <th>ssid</th>\n",
       "      <th>account</th>\n",
       "      <th>mac</th>\n",
       "      <th>linktime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>084f-0a37-30c0</td>\n",
       "      <td>084f0a3730c0</td>\n",
       "      <td>iLZU</td>\n",
       "      <td>qmleu16</td>\n",
       "      <td>ae2887</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>084f-0a37-30c0</td>\n",
       "      <td>084f0a3730c0</td>\n",
       "      <td>iLZU</td>\n",
       "      <td>eamrw19</td>\n",
       "      <td>fa341c</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0_no_name</td>\n",
       "      <td>5cc9999a3c80</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>9ce33f</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0_no_name</td>\n",
       "      <td>5cc9999a3c80</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>3ca616</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0_no_name</td>\n",
       "      <td>5cc9999a3c80</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>182195</td>\n",
       "      <td>08:20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27545</th>\n",
       "      <td>yz_youyongguan_01</td>\n",
       "      <td>60da838bc730</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>f89a78</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27546</th>\n",
       "      <td>yz_youyongguan_02</td>\n",
       "      <td>60da838bba10</td>\n",
       "      <td>ChinaNet-WiFi</td>\n",
       "      <td></td>\n",
       "      <td>74adb7</td>\n",
       "      <td>08:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27547</th>\n",
       "      <td>yz_youyongguan_02</td>\n",
       "      <td>60da838bba10</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>cc2d83</td>\n",
       "      <td>08:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27548</th>\n",
       "      <td>yz_youyongguan_03</td>\n",
       "      <td>60da838bcc10</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>308454</td>\n",
       "      <td>08:20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27549</th>\n",
       "      <td>yz_youyongguan_06</td>\n",
       "      <td>60da838bbe90</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>084acf</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27550 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 ap_name        ap_mac           ssid  account     mac  \\\n",
       "0         084f-0a37-30c0  084f0a3730c0           iLZU  qmleu16  ae2887   \n",
       "1         084f-0a37-30c0  084f0a3730c0           iLZU  eamrw19  fa341c   \n",
       "2              0_no_name  5cc9999a3c80            LZU           9ce33f   \n",
       "3              0_no_name  5cc9999a3c80            LZU           3ca616   \n",
       "4              0_no_name  5cc9999a3c80            LZU           182195   \n",
       "...                  ...           ...            ...      ...     ...   \n",
       "27545  yz_youyongguan_01  60da838bc730            LZU           f89a78   \n",
       "27546  yz_youyongguan_02  60da838bba10  ChinaNet-WiFi           74adb7   \n",
       "27547  yz_youyongguan_02  60da838bba10            LZU           cc2d83   \n",
       "27548  yz_youyongguan_03  60da838bcc10            LZU           308454   \n",
       "27549  yz_youyongguan_06  60da838bbe90            LZU           084acf   \n",
       "\n",
       "       linktime  \n",
       "0      09:00:00  \n",
       "1      09:00:00  \n",
       "2      09:00:00  \n",
       "3      09:00:00  \n",
       "4      08:20:00  \n",
       "...         ...  \n",
       "27545  09:00:00  \n",
       "27546  08:40:00  \n",
       "27547  08:40:00  \n",
       "27548  08:20:00  \n",
       "27549  09:00:00  \n",
       "\n",
       "[27550 rows x 6 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['linktime'] = data['linktime'].apply(lambda x: x.split(' ')[1])\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 把ap_name中楼缩写替换为楼名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ap_name</th>\n",
       "      <th>ap_mac</th>\n",
       "      <th>ssid</th>\n",
       "      <th>account</th>\n",
       "      <th>mac</th>\n",
       "      <th>linktime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>084f-0a37-30c0</td>\n",
       "      <td>084f0a3730c0</td>\n",
       "      <td>iLZU</td>\n",
       "      <td>qmleu16</td>\n",
       "      <td>ae2887</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>084f-0a37-30c0</td>\n",
       "      <td>084f0a3730c0</td>\n",
       "      <td>iLZU</td>\n",
       "      <td>eamrw19</td>\n",
       "      <td>fa341c</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0_no_name</td>\n",
       "      <td>5cc9999a3c80</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>9ce33f</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0_no_name</td>\n",
       "      <td>5cc9999a3c80</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>3ca616</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0_no_name</td>\n",
       "      <td>5cc9999a3c80</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>182195</td>\n",
       "      <td>08:20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27545</th>\n",
       "      <td>游泳馆</td>\n",
       "      <td>60da838bc730</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>f89a78</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27546</th>\n",
       "      <td>游泳馆</td>\n",
       "      <td>60da838bba10</td>\n",
       "      <td>ChinaNet-WiFi</td>\n",
       "      <td></td>\n",
       "      <td>74adb7</td>\n",
       "      <td>08:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27547</th>\n",
       "      <td>游泳馆</td>\n",
       "      <td>60da838bba10</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>cc2d83</td>\n",
       "      <td>08:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27548</th>\n",
       "      <td>游泳馆</td>\n",
       "      <td>60da838bcc10</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>308454</td>\n",
       "      <td>08:20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27549</th>\n",
       "      <td>游泳馆</td>\n",
       "      <td>60da838bbe90</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>084acf</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27550 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              ap_name        ap_mac           ssid  account     mac  linktime\n",
       "0      084f-0a37-30c0  084f0a3730c0           iLZU  qmleu16  ae2887  09:00:00\n",
       "1      084f-0a37-30c0  084f0a3730c0           iLZU  eamrw19  fa341c  09:00:00\n",
       "2           0_no_name  5cc9999a3c80            LZU           9ce33f  09:00:00\n",
       "3           0_no_name  5cc9999a3c80            LZU           3ca616  09:00:00\n",
       "4           0_no_name  5cc9999a3c80            LZU           182195  08:20:00\n",
       "...               ...           ...            ...      ...     ...       ...\n",
       "27545             游泳馆  60da838bc730            LZU           f89a78  09:00:00\n",
       "27546             游泳馆  60da838bba10  ChinaNet-WiFi           74adb7  08:40:00\n",
       "27547             游泳馆  60da838bba10            LZU           cc2d83  08:40:00\n",
       "27548             游泳馆  60da838bcc10            LZU           308454  08:20:00\n",
       "27549             游泳馆  60da838bbe90            LZU           084acf  09:00:00\n",
       "\n",
       "[27550 rows x 6 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def change(ap_name):\n",
    "    chip = set(ap_name.split('_'))\n",
    "    if 'tst' in chip:\n",
    "        return '天山堂'\n",
    "    elif 'hit' in chip:\n",
    "        return '贺兰堂'\n",
    "    elif 'klt' in chip:\n",
    "        return '昆仑堂'\n",
    "    elif 'youyongguan' in chip:\n",
    "        return '游泳馆'\n",
    "    else:\n",
    "        return ap_name\n",
    "    \n",
    "old_ap_name = data['ap_name']\n",
    "data['ap_name'] = data['ap_name'].apply(change)\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 对账号进行脱敏处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ap_name</th>\n",
       "      <th>ap_mac</th>\n",
       "      <th>ssid</th>\n",
       "      <th>account</th>\n",
       "      <th>mac</th>\n",
       "      <th>linktime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>084f-0a37-30c0</td>\n",
       "      <td>084f0a3730c0</td>\n",
       "      <td>iLZU</td>\n",
       "      <td>rnmfv16</td>\n",
       "      <td>ae2887</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>084f-0a37-30c0</td>\n",
       "      <td>084f0a3730c0</td>\n",
       "      <td>iLZU</td>\n",
       "      <td>fbnsx19</td>\n",
       "      <td>fa341c</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0_no_name</td>\n",
       "      <td>5cc9999a3c80</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>9ce33f</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0_no_name</td>\n",
       "      <td>5cc9999a3c80</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>3ca616</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0_no_name</td>\n",
       "      <td>5cc9999a3c80</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>182195</td>\n",
       "      <td>08:20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27545</th>\n",
       "      <td>游泳馆</td>\n",
       "      <td>60da838bc730</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>f89a78</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27546</th>\n",
       "      <td>游泳馆</td>\n",
       "      <td>60da838bba10</td>\n",
       "      <td>ChinaNet-WiFi</td>\n",
       "      <td></td>\n",
       "      <td>74adb7</td>\n",
       "      <td>08:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27547</th>\n",
       "      <td>游泳馆</td>\n",
       "      <td>60da838bba10</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>cc2d83</td>\n",
       "      <td>08:40:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27548</th>\n",
       "      <td>游泳馆</td>\n",
       "      <td>60da838bcc10</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>308454</td>\n",
       "      <td>08:20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27549</th>\n",
       "      <td>游泳馆</td>\n",
       "      <td>60da838bbe90</td>\n",
       "      <td>LZU</td>\n",
       "      <td></td>\n",
       "      <td>084acf</td>\n",
       "      <td>09:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27550 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              ap_name        ap_mac           ssid  account     mac  linktime\n",
       "0      084f-0a37-30c0  084f0a3730c0           iLZU  rnmfv16  ae2887  09:00:00\n",
       "1      084f-0a37-30c0  084f0a3730c0           iLZU  fbnsx19  fa341c  09:00:00\n",
       "2           0_no_name  5cc9999a3c80            LZU           9ce33f  09:00:00\n",
       "3           0_no_name  5cc9999a3c80            LZU           3ca616  09:00:00\n",
       "4           0_no_name  5cc9999a3c80            LZU           182195  08:20:00\n",
       "...               ...           ...            ...      ...     ...       ...\n",
       "27545             游泳馆  60da838bc730            LZU           f89a78  09:00:00\n",
       "27546             游泳馆  60da838bba10  ChinaNet-WiFi           74adb7  08:40:00\n",
       "27547             游泳馆  60da838bba10            LZU           cc2d83  08:40:00\n",
       "27548             游泳馆  60da838bcc10            LZU           308454  08:20:00\n",
       "27549             游泳馆  60da838bbe90            LZU           084acf  09:00:00\n",
       "\n",
       "[27550 rows x 6 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def cleaning(x):\n",
    "    s_list = []\n",
    "    for i in x:\n",
    "        if (64<ord(i)<91) or (96<ord(i)<122):\n",
    "            i = chr(ord(i) + 1)\n",
    "        elif ord(i) == 91:\n",
    "            i = 'A'\n",
    "        elif ord(i) == 122:\n",
    "            i = 'a'\n",
    "        s_list.append(i)\n",
    "    return ''.join(s_list)\n",
    "\n",
    "data1 = data.copy()\n",
    "data1['account'] = data1.account.apply(cleaning)\n",
    "data1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 统计题目2中每个楼中有多少终端"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>device_num</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ap_name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>yzsw_cheku</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>天山堂</th>\n",
       "      <td>2921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>昆仑堂</th>\n",
       "      <td>586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>游泳馆</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            device_num\n",
       "ap_name               \n",
       "yzsw_cheku           7\n",
       "天山堂               2921\n",
       "昆仑堂                586\n",
       "游泳馆                  5"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_count = data.iloc[:, :2].groupby('ap_name').count()[-4:]\n",
    "ap_count.columns = ['device_num']\n",
    "ap_count"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 展现天山堂各教室终端数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a504</th>\n",
       "      <td>170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a602</th>\n",
       "      <td>115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a502</th>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b503</th>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b602</th>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a409</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a405</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b405</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>a605</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b401</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>113 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      SUM\n",
       "a504  170\n",
       "a602  115\n",
       "a502   99\n",
       "b503   93\n",
       "b602   92\n",
       "...   ...\n",
       "a409    1\n",
       "a405    1\n",
       "b405    1\n",
       "a605    1\n",
       "b401    1\n",
       "\n",
       "[113 rows x 1 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "newDF = pd.read_json('lzuwifi.json',lines=\"True\")    \n",
    "dfName = newDF['ap_name'].str.split('_|-',expand=True)\n",
    "Count = dfName.loc[dfName[1] == 'tst'][3].value_counts()\n",
    "result = Count.to_frame(name = 'SUM')\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
